package db;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

import org.hsqldb.Types;

import util.MyLogger;

public class DBSQLUtil extends MyLogger{

	protected final static String SQL_NOT_NULL = " NOT NULL ",
	SQL_UNIQUE = " UNIQUE ", SQL_DEFAULT = " DEFAULT ",
	SQL_PRIMARY_KEY = " PRIMARY KEY ",
	SQL_FOREIGN_KEY = " FOREIGN KEY ", SQL_REFERENCES = " REFERENCES ",
	SQL_CHECK = " CHECK ", SQL_IDENTITY = " GENERATED BY DEFAULT AS IDENTITY(START WITH 0)", 
	SQL_IDENTITY_PRIMARY_KEY=SQL_IDENTITY+SQL_PRIMARY_KEY,
	SQL_ORDER_ASCENDING=" ASC ", SQL_ORDER_DESCENDING=" DESC ", 
	SQL_CURRENT_DATE=" CURRENT_DATE ",SQL_OPERATOR_LIKE=" LIKE ";
	
	protected final static String START_DATE_COL="start_date", END_DATE_COL= "end_date";
	
	/**
	 * Returns the part of an SQL-Statement necessary to reference primary key
	 * in a second table. Primary used for create table.
	 * 
	 * @param ref_table
	 * @param col
	 * @return
	 */
	public static String getSQLForeignKeyReference(String ref_table, String col) {
		return getSQLForeignKeyReference(ref_table, col, col);
	}

	/**
	 * Returns the part of an SQL-Statement necessary to reference primary key
	 * in a second table. Primary used for create table.
	 * 
	 * @param ref_table
	 * @param col
	 * @param ref_col
	 * @return
	 */
	public static String getSQLForeignKeyReference(String ref_table,
			String col, String ref_col) {
		return " , " + SQL_FOREIGN_KEY + "(" + col + ")" + SQL_REFERENCES
				+ ref_table + "(" + ref_col + ") ";
	}

	/**
	 * Returns the given condition correct surround.
	 * 
	 * @param condition
	 * @return
	 */
	public static String getSQLCheck(String condition) {
		return " , " + SQL_CHECK + "(" + condition + ") ";
	}
	
	public static String getSQLCheckEndDate(){
		return getSQLCheck(START_DATE_COL+" <= "+END_DATE_COL);
	}
	
	public static String getSQLCheckStartDate(){
		return getSQLCheck(START_DATE_COL+" <= CURRENT_DATE");
	}
	
	public static String getSQLUnique(String col){
		return " , " + SQL_UNIQUE + "("+col+") ";
	}
	
	public static String getSQLDefaultValue(String value ){
		return SQL_DEFAULT+"("+value+")";
	}

	/**
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public static ArrayList<ArrayList<Object>> getResultSetAsArrayList(ResultSet rs) throws SQLException{
		return getRowsAsArrayList(rs, true);
	}
	
	public static ArrayList<ArrayList<Object>> getRowsAsArrayList(ResultSet rs, boolean header) throws SQLException{
		ArrayList<ArrayList<Object>> result=new ArrayList<ArrayList<Object>>();
		if(header)result.add(getColumnNamesAsArrayList(rs));
		while (rs.next()) {
			result.add(getRowAsArrayList(rs));
		}return result;
	}
	
	public static ArrayList<Object> getRowAsArrayList(ResultSet rs) throws SQLException{
		int maxCol = rs.getMetaData().getColumnCount();
		ArrayList<Object> temp=new ArrayList<Object>();
		if(rs.isBeforeFirst())rs.next();
		for (int i = 1; i <= maxCol; i++) {
			temp.add(rs.getObject(i));
		}
		return temp;
	}
	
	public static ArrayList<ArrayList<Object>> getRowsAsObjectArrayList(ResultSet rs) throws SQLException{
		ArrayList<ArrayList<Object>> result=new ArrayList<ArrayList<Object>>();
		while (rs.next()) {
			result.add(getRowAsObjectArrayList(rs));
		}return result;
	}
	
	public static ArrayList<Object> getRowAsObjectArrayList(ResultSet rs) throws SQLException{
		int maxCol = rs.getMetaData().getColumnCount();
		ArrayList<Object> temp=new ArrayList<Object>();
		if(rs.isBeforeFirst())rs.next();
		for (int i = 1; i <= maxCol; i++) {
			temp.add(rs.getObject(i));
		}
		return temp;
	}
	
	public static ArrayList<Object> getColumnNamesAsArrayList(ResultSet rs) throws SQLException{
		ResultSetMetaData rsmd = rs.getMetaData();
		int maxCol = rsmd.getColumnCount();
		ArrayList<Object> temp = new ArrayList<Object>();
		for (int i = 1; i <= maxCol; i++) {
			temp.add(rsmd.getColumnName(i));
		}
		return temp;
	}

	public static void setValueInPreparedStatement(PreparedStatement ps,
			Object value, int i) throws SQLException {
		if(value==null)
			ps.setNull(i, Types.NULL);
		else if (value.getClass() == Boolean.class)
			ps.setBoolean(i, (Boolean) value);
		else if (value.getClass() == String.class)
			ps.setString(i, String.valueOf(value));
		else if (value.getClass() == Integer.class)
			ps.setInt(i, (Integer) value);
		else if (value.getClass() == Long.class)
			ps.setLong(i, (Long) value);
		else if (value.getClass() == Byte.class)
			ps.setByte(i, (Byte) value);
		else if (value.getClass() == byte[].class)
			ps.setBytes(i, (byte[]) value);
		else if (value.getClass()==Date.class)
			ps.setDate(i, (Date)value);
		else
			ps.setObject(i, value);
	}
	
	public static void setValuesInStatement(PreparedStatement ps, Object[] values) throws SQLException{
		for(int i=0;i<values.length;i++){
			setValueInPreparedStatement(ps, values[i], i+1);
		}
	}
	
	protected static void closeStatement(PreparedStatement ps){
		try {
			if(ps!=null)ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	protected static void closeResult(ResultSet rs){
		try {
			if(rs!=null)rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	protected static String getColumnAsString(String[] columnArray){
		String column="*";
		if(columnArray!=null){
			column=((columnArray.length>0)?"":column);
			for(int i=0;i<columnArray.length;i++)
				column+= (i==0?"":",") + columnArray[i];
		}
		return column;
	}
	
	protected static String getConditionAsString(String[] conditionArray){
		return getConditionAsString(conditionArray, "=");
	}
	
	protected static String getConditionAsString(String[] conditionArray,String operator){
		String condition="";
		if (conditionArray != null) {
			condition = ((conditionArray.length > 0) ? " WHERE " : condition);
			for (int i = 0; i < conditionArray.length; i++) {
				condition += (i == 0 ? "" : " AND ") + conditionArray[i]+operator+" ?";
			}
		}
		return condition;
	} 
	
	protected static String getTableAsString(String[] tableArray, String[][] onColsArray){
		String table=tableArray[0];
		for (int i = 1; i < tableArray.length; i++) {
			table += " JOIN " + tableArray[i]+" ON ";
			table+= onColsArray[i-1][0];
			for(int j=1;j<onColsArray[i].length;j++)
				table +=  ", "+onColsArray[i-1][j];
		}
		return table;
	}
	
	protected static String getOrderAsString(String orderCol, String direc){
		String order="";
		if(orderCol!=null){
			if(!orderCol.equalsIgnoreCase("")){
				order=" ORDER BY " + orderCol;
				if(direc!=null) 
					if(!direc.equalsIgnoreCase("")) 
						order+=" " + direc;
			}
		}
		return order;
	}

	public static ResultSet getSimpleSelectResults(String[] columnArray, String[] conditionArray, Object[] keyArray, 
			String[] tableArray, String[][] onColsArray, Object[] onColsValueArray, String orderCol, String direc)
			throws SQLException, ClassNotFoundException {
		return getSimpleSelectResults(columnArray, conditionArray, "=", keyArray, 
				tableArray, onColsArray, onColsValueArray, orderCol, direc);
	}
	
	/**
	 * 
	 * @param columnArray null returns results over all columns
	 * @param conditionArray
	 * @param keyArray
	 * @param orderColArray
	 * @param direc
	 * @return ResultSet for the given parameters
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public static ResultSet getSimpleSelectResults(String[] columnArray, String[] conditionArray, String operator, Object[] keyArray, 
			String[] tableArray, String[][] onColsArray, Object[] onColsValueArray, String orderCol, String direc) 
			throws SQLException, ClassNotFoundException {
		if(tableArray==null)
			throw new IllegalArgumentException("Tables may not be empty!");
		if(tableArray.length>1){
			if(onColsArray==null)
				throw new IllegalArgumentException("Conditions Columns for JOIN may not be empty!");
			else if(tableArray.length!=onColsArray.length-1)
				throw new IllegalArgumentException("Conditions Columns for JOIN may not be empty!");
		}
		String column="",condition="",table="",order="";
		
		// Declares the columns in the returning ResultSet  for the statement
		column=getColumnAsString(columnArray);
		
		// Prepares the condition
		condition=getConditionAsString(conditionArray);
	
		// Prepares tables
		table=getTableAsString(tableArray, onColsArray);
		
		order=getOrderAsString(orderCol, direc);
		
		String statement= "SELECT " + column + " FROM "
		+ table + condition + order+";";
		
		PreparedStatement ps=DBConnection.getConnection().prepareStatement(statement);
		// Now set the values in the prepared statement
		int pos=1;
		if(keyArray!=null){
			for(Object key:keyArray){
				setValueInPreparedStatement(ps, key, pos);
				pos++;
			}
		}
		if(onColsValueArray!=null){
			for(Object key:onColsValueArray){
				setValueInPreparedStatement(ps, key, pos);
				pos++;
			}
		}
	
		return ps.executeQuery();
	}
	
	public static void printResultSet(ResultSet rs) throws SQLException{
		ResultSetMetaData rsmd=rs.getMetaData();
		int maxCol=rsmd.getColumnCount();
		for (int i = 1; i <= maxCol; i++)
			System.out.print(" | "+rsmd.getColumnName(i));
		
		System.out.println(" | ");
		
		while (rs.next()) {
			String s="";
			for (int i = 1; i <= maxCol; i++)
				s += rs.getString(i) + " | ";
			System.out.println(s);
		}
	}
	
	public static String getAbsoluteColumn(String table,String col){
		return table+"."+col;
	}
}
